package vendas.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vendas.dto.BuyerType;
import vendas.dto.CurrencyType;
import vendas.dto.Order;
import vendas.dto.OrderStatus;
import vendas.dto.PaymentStatus;
import vendas.dto.SellStatus;

public class OrderDAO extends DataAccessObject {

    public static Order findByPK(int userId, int orderId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT O.CD_USER            CD_USER           ");
        sbSql.append("     , U.TX_NAME            TX_NAME_USER      ");
        sbSql.append("     , O.CD_ORDER           CD_ORDER          ");
        sbSql.append("     , O.CD_SELLER          CD_SELLER         ");
        sbSql.append("     , S.TX_NAME            TX_NAME_SELLER    ");
        sbSql.append("     , O.CD_PRODUCT         CD_PRODUCT        ");
        sbSql.append("     , P.TX_NAME            TX_NAME_PRODUCT   ");
        sbSql.append("     , O.ID_STATUS          ID_ORDER_STATUS   ");
        sbSql.append("     , O.DT_ORDER           DT_ORDER          ");
        sbSql.append("     , O.ID_CURRENCY        ID_CURRENCY       ");
        sbSql.append("     , O.VL_BUY             VL_BUY            ");
        sbSql.append("     , O.VL_SHIPPING        VL_SHIPPING       ");
        sbSql.append("     , O.VL_TAX             VL_TAX            ");
        sbSql.append("     , O.VL_CURRENCY        VL_CURRENCY       ");
        sbSql.append("     , O.DT_PAYMENT         DT_PAYMENT        ");
        sbSql.append("     , O.ID_PAYMENT_STATUS  ID_PAYMENT_STATUS ");
        sbSql.append("     , O.DT_ARRIVE          DT_ARRIVE         ");
        sbSql.append("     , O.DT_SELL            DT_SELL           ");
        sbSql.append("     , O.VL_SELL            VL_SELL           ");
        sbSql.append("     , O.ID_TYPE_BUYER      ID_TYPE_BUYER     ");
        sbSql.append("     , O.TX_BUYER           TX_BUYER          ");
        sbSql.append("     , O.ID_SELL_STATUS     ID_SELL_STATUS    ");
        sbSql.append("     , O.TX_ORDER_ID        TX_ORDER_ID       ");
        sbSql.append("     , O.TX_SHIPPING_ID     TX_SHIPPING_ID    ");
        sbSql.append("     , O.TX_COMENTS         TX_COMENTS        ");
        sbSql.append("  FROM TB_ORDER   O ");
        sbSql.append("     , TB_USER    U ");
        sbSql.append("     , TB_SELLER  S ");
        sbSql.append("     , TB_PRODUCT P ");
        sbSql.append(" WHERE O.CD_USER    = U.CD_USER     ");
        sbSql.append("   AND O.CD_SELLER  = S.CD_SELLER   ");
        sbSql.append("   AND O.CD_PRODUCT = P.CD_PRODUCT  ");
        sbSql.append("   AND O.CD_USER   = ? ");
        sbSql.append("   AND O.CD_ORDER  = ? ");

        Order order = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, userId);
            setInt(ps, 2, orderId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                order = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return order;
    }

    public static List<Order> findAll(int userId, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT O.CD_USER            CD_USER           ");
        sbSql.append("     , U.TX_NAME            TX_NAME_USER      ");
        sbSql.append("     , O.CD_ORDER           CD_ORDER          ");
        sbSql.append("     , O.CD_SELLER          CD_SELLER         ");
        sbSql.append("     , S.TX_NAME            TX_NAME_SELLER    ");
        sbSql.append("     , O.CD_PRODUCT         CD_PRODUCT        ");
        sbSql.append("     , P.TX_NAME            TX_NAME_PRODUCT   ");
        sbSql.append("     , O.ID_STATUS          ID_ORDER_STATUS   ");
        sbSql.append("     , O.DT_ORDER           DT_ORDER          ");
        sbSql.append("     , O.ID_CURRENCY        ID_CURRENCY       ");
        sbSql.append("     , O.VL_BUY             VL_BUY            ");
        sbSql.append("     , O.VL_SHIPPING        VL_SHIPPING       ");
        sbSql.append("     , O.VL_TAX             VL_TAX            ");
        sbSql.append("     , O.VL_CURRENCY        VL_CURRENCY       ");
        sbSql.append("     , O.DT_PAYMENT         DT_PAYMENT        ");
        sbSql.append("     , O.ID_PAYMENT_STATUS  ID_PAYMENT_STATUS ");
        sbSql.append("     , O.DT_ARRIVE          DT_ARRIVE         ");
        sbSql.append("     , O.DT_SELL            DT_SELL           ");
        sbSql.append("     , O.VL_SELL            VL_SELL           ");
        sbSql.append("     , O.ID_TYPE_BUYER      ID_TYPE_BUYER     ");
        sbSql.append("     , O.TX_BUYER           TX_BUYER          ");
        sbSql.append("     , O.ID_SELL_STATUS     ID_SELL_STATUS    ");
        sbSql.append("     , O.TX_ORDER_ID        TX_ORDER_ID       ");
        sbSql.append("     , O.TX_SHIPPING_ID     TX_SHIPPING_ID    ");
        sbSql.append("     , O.TX_COMENTS         TX_COMENTS        ");
        sbSql.append("  FROM TB_ORDER   O ");
        sbSql.append("     , TB_USER    U ");
        sbSql.append("     , TB_SELLER  S ");
        sbSql.append("     , TB_PRODUCT P ");
        sbSql.append(" WHERE O.CD_USER    = U.CD_USER     ");
        sbSql.append("   AND O.CD_SELLER  = S.CD_SELLER   ");
        sbSql.append("   AND O.CD_PRODUCT = P.CD_PRODUCT  ");
        sbSql.append("   AND O.CD_USER     = ? ");
        sbSql.append(" ORDER BY O.CD_ORDER ");
        List<Order> ordersList = new ArrayList<Order>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Order order = fromResultSet(rs);
                ordersList.add(order);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.findAll (" + sqle.getMessage() + ")");
        }
        return ordersList;
    }

    public static List<Order> findByFilter(OrderFilter filter, Connection conn) throws TransactionException {

        return findByFilter(filter, false, conn);
    }

    public static List<Order> findByFilter(OrderFilter filter, boolean pendentOnly, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT O.CD_USER            CD_USER           ");
        sbSql.append("     , U.TX_NAME            TX_NAME_USER      ");
        sbSql.append("     , O.CD_ORDER           CD_ORDER          ");
        sbSql.append("     , O.CD_SELLER          CD_SELLER         ");
        sbSql.append("     , S.TX_NAME            TX_NAME_SELLER    ");
        sbSql.append("     , O.CD_PRODUCT         CD_PRODUCT        ");
        sbSql.append("     , P.TX_NAME            TX_NAME_PRODUCT   ");
        sbSql.append("     , O.ID_STATUS          ID_ORDER_STATUS   ");
        sbSql.append("     , O.DT_ORDER           DT_ORDER          ");
        sbSql.append("     , O.ID_CURRENCY        ID_CURRENCY       ");
        sbSql.append("     , O.VL_BUY             VL_BUY            ");
        sbSql.append("     , O.VL_SHIPPING        VL_SHIPPING       ");
        sbSql.append("     , O.VL_TAX             VL_TAX            ");
        sbSql.append("     , O.VL_CURRENCY        VL_CURRENCY       ");
        sbSql.append("     , O.DT_PAYMENT         DT_PAYMENT        ");
        sbSql.append("     , O.ID_PAYMENT_STATUS  ID_PAYMENT_STATUS ");
        sbSql.append("     , O.DT_ARRIVE          DT_ARRIVE         ");
        sbSql.append("     , O.DT_SELL            DT_SELL           ");
        sbSql.append("     , O.VL_SELL            VL_SELL           ");
        sbSql.append("     , O.ID_TYPE_BUYER      ID_TYPE_BUYER     ");
        sbSql.append("     , O.TX_BUYER           TX_BUYER          ");
        sbSql.append("     , O.ID_SELL_STATUS     ID_SELL_STATUS    ");
        sbSql.append("     , O.TX_ORDER_ID        TX_ORDER_ID       ");
        sbSql.append("     , O.TX_SHIPPING_ID     TX_SHIPPING_ID    ");
        sbSql.append("     , O.TX_COMENTS         TX_COMENTS        ");
        sbSql.append("  FROM TB_ORDER   O ");
        sbSql.append("     , TB_USER    U ");
        sbSql.append("     , TB_SELLER  S ");
        sbSql.append("     , TB_PRODUCT P ");
        sbSql.append(" WHERE O.CD_USER    = U.CD_USER     ");
        sbSql.append("   AND O.CD_SELLER  = S.CD_SELLER   ");
        sbSql.append("   AND O.CD_PRODUCT = P.CD_PRODUCT  ");
        if (filter != null && filter.hasUser()) {
            sbSql.append(" AND O.CD_USER = ? ");
        }
        if (filter != null && filter.hasSeller()) {
            sbSql.append(" AND O.CD_SELLER = ? ");
        }
        if (filter != null && filter.hasProduct()) {
            sbSql.append(" AND O.CD_PRODUCT = ? ");
        }
        if (filter != null && filter.hasOrderStatus()) {
            if (filter.getNoOrderStatus()) {
                sbSql.append(" AND O.ID_STATUS != ? ");
            } else {
                sbSql.append(" AND O.ID_STATUS = ? ");
            }
        }
        if (filter != null && filter.hasPaymentStatus()) {
            sbSql.append(" AND O.ID_PAYMENT_STATUS = ? ");
        }
        if (filter != null && filter.hasSellStatus()) {
            sbSql.append(" AND O.ID_SELL_STATUS = ? ");
        }
        if (filter != null && filter.hasBuyDate()) {
            sbSql.append(" AND O.DT_ORDER = ? ");
        }
        if (filter != null && filter.hasSellerOrderId()) {
            String strSellerOrderId = filter.getSellerOrderId().toLowerCase();
            sbSql.append(" AND LOWER(O.TX_ORDER_ID) like '%" + strSellerOrderId + "%' ");
        }
        if (pendentOnly) {
            sbSql.append(" AND (O.ID_STATUS != ? OR O.ID_PAYMENT_STATUS != ? OR O.ID_SELL_STATUS != ?) ");
        }
        sbSql.append(" ORDER BY O.CD_ORDER ");
        List<Order> ordersList = new ArrayList<Order>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            if (filter != null && filter.hasUser()) {
                setInt(ps, pos++, filter.getUser().getId());
            }
            if (filter != null && filter.hasSeller()) {
                setInt(ps, pos++, filter.getSeller().getId());
            }
            if (filter != null && filter.hasProduct()) {
                setInt(ps, pos++, filter.getProduct().getId());
            }
            if (filter != null && filter.hasOrderStatus()) {
                setInt(ps, pos++, filter.getOrderStatus().getId());
            }
            if (filter != null && filter.hasPaymentStatus()) {
                setInt(ps, pos++, filter.getPaymentStatus().getId());
            }
            if (filter != null && filter.hasSellStatus()) {
                setInt(ps, pos++, filter.getSellStatus().getId());
            }
            if (filter != null && filter.hasBuyDate()) {
                setDate(ps, pos++, filter.getBuyDate());
            }
            if (pendentOnly) {
                setInt(ps, pos++, OrderStatus.SOLD.getId());
                setInt(ps, pos++, SellStatus.RECEIVE_PAYMENT.getId());
                setInt(ps, pos++, PaymentStatus.PAY.getId());
            }

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Order order = fromResultSet(rs);
                ordersList.add(order);
            }
            close(rs);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.findByFilter (" + sqle.getMessage() + ")");
        }
        return ordersList;
    }

    public static void insert(Order order, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO TB_ORDER     ");
        sbSql.append("     ( CD_USER           ");
        sbSql.append("     , CD_ORDER          ");
        sbSql.append("     , CD_SELLER         ");
        sbSql.append("     , CD_PRODUCT        ");
        sbSql.append("     , ID_STATUS         ");
        sbSql.append("     , DT_ORDER          ");
        sbSql.append("     , ID_CURRENCY       ");
        sbSql.append("     , VL_BUY            ");
        sbSql.append("     , VL_SHIPPING       ");
        sbSql.append("     , VL_TAX            ");
        sbSql.append("     , VL_CURRENCY       ");
        sbSql.append("     , DT_PAYMENT        ");
        sbSql.append("     , ID_PAYMENT_STATUS ");
        sbSql.append("     , DT_ARRIVE         ");
        sbSql.append("     , DT_SELL           ");
        sbSql.append("     , VL_SELL           ");
        sbSql.append("     , ID_TYPE_BUYER     ");
        sbSql.append("     , TX_BUYER          ");
        sbSql.append("     , ID_SELL_STATUS    ");
        sbSql.append("     , TX_ORDER_ID       ");
        sbSql.append("     , TX_SHIPPING_ID    ");
        sbSql.append("     , TX_COMENTS        ");
        sbSql.append(") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, order.getUser().getId());
            setInt(ps, pos++, order.getId());
            setInt(ps, pos++, order.getSeller().getId());
            setInt(ps, pos++, order.getProduct().getId());
            setInt(ps, pos++, order.getStatus().getId());
            setDate(ps, pos++, order.getOrderDate());
            setInt(ps, pos++, order.getCurrencyType().getId());
            setFloat(ps, pos++, order.getBuyValue());
            setFloat(ps, pos++, order.getShippingValue());
            setFloat(ps, pos++, order.getTaxValue());
            setFloat(ps, pos++, order.getCurrencyValue());
            setDate(ps, pos++, order.getPaymentDate());
            setInt(ps, pos++, order.getPaymentStatus().getId());
            setDate(ps, pos++, order.getArriveDate());
            setDate(ps, pos++, order.getSellDate());
            setFloat(ps, pos++, order.getSellValue());
            setInt(ps, pos++, order.getBuyerType().getId(), true);
            setString(ps, pos++, order.getBuyer());
            setInt(ps, pos++, order.getSellStatus().getId(), true);
            setString(ps, pos++, order.getSellerOrderId());
            setString(ps, pos++, order.getShippingId());
            setString(ps, pos++, order.getComents());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(Order order, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE TB_ORDER              ");
        sbSql.append("   SET CD_SELLER         = ? ");
        sbSql.append("     , CD_PRODUCT        = ? ");
        sbSql.append("     , ID_STATUS         = ? ");
        sbSql.append("     , DT_ORDER          = ? ");
        sbSql.append("     , ID_CURRENCY       = ? ");
        sbSql.append("     , VL_BUY            = ? ");
        sbSql.append("     , VL_SHIPPING       = ? ");
        sbSql.append("     , VL_TAX            = ? ");
        sbSql.append("     , VL_CURRENCY       = ? ");
        sbSql.append("     , DT_PAYMENT        = ? ");
        sbSql.append("     , ID_PAYMENT_STATUS = ? ");
        sbSql.append("     , DT_ARRIVE         = ? ");
        sbSql.append("     , DT_SELL           = ? ");
        sbSql.append("     , VL_SELL           = ? ");
        sbSql.append("     , ID_TYPE_BUYER     = ? ");
        sbSql.append("     , TX_BUYER          = ? ");
        sbSql.append("     , ID_SELL_STATUS    = ? ");
        sbSql.append("     , TX_ORDER_ID       = ? ");
        sbSql.append("     , TX_SHIPPING_ID    = ? ");
        sbSql.append("     , TX_COMENTS        = ? ");
        sbSql.append(" WHERE CD_USER     = ? ");
        sbSql.append("   AND CD_ORDER    = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, order.getSeller().getId());
            setInt(ps, pos++, order.getProduct().getId());
            setInt(ps, pos++, order.getStatus().getId());
            setDate(ps, pos++, order.getOrderDate());
            setInt(ps, pos++, order.getCurrencyType().getId());
            setFloat(ps, pos++, order.getBuyValue());
            setFloat(ps, pos++, order.getShippingValue());
            setFloat(ps, pos++, order.getTaxValue());
            setFloat(ps, pos++, order.getCurrencyValue());
            setDate(ps, pos++, order.getPaymentDate());
            setInt(ps, pos++, order.getPaymentStatus().getId());
            setDate(ps, pos++, order.getArriveDate());
            setDate(ps, pos++, order.getSellDate());
            setFloat(ps, pos++, order.getSellValue());
            setInt(ps, pos++, order.getBuyerType().getId(), true);
            setString(ps, pos++, order.getBuyer());
            setInt(ps, pos++, order.getSellStatus().getId(), true);
            setString(ps, pos++, order.getSellerOrderId());
            setString(ps, pos++, order.getShippingId());
            setString(ps, pos++, order.getComents());
            setInt(ps, pos++, order.getUser().getId());
            setInt(ps, pos++, order.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(Order order, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM TB_ORDER   ");
        sbSql.append(" WHERE CD_USER     = ? ");
        sbSql.append("   AND CD_ORDER    = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, order.getUser().getId());
            setInt(ps, pos++, order.getId());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("OrderDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Connection conn, int userId, int iRange, int eRange) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_ORDER) AS CD_MAX ");
            sbSql.append("  FROM TB_ORDER        ");
            sbSql.append(" WHERE CD_USER  = ? ");
            sbSql.append("   AND CD_ORDER > ? ");
            sbSql.append("   AND CD_ORDER < ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, userId);
            setInt(ps, 2, iRange);
            setInt(ps, 3, eRange);
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return (max != 0 ? max : iRange) + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static Order fromResultSet(ResultSet rs) throws SQLException {

        // ORDER
        Order order = new Order();
        order.getUser().setId(getInt(rs, "CD_USER"));
        order.getUser().setName(getString(rs, "TX_NAME_USER"));
        order.setId(getInt(rs, "CD_ORDER"));
        order.getSeller().setId(getInt(rs, "CD_SELLER"));
        order.getSeller().setName(getString(rs, "TX_NAME_SELLER"));
        order.getProduct().setId(getInt(rs, "CD_PRODUCT"));
        order.getProduct().setName(getString(rs, "TX_NAME_PRODUCT"));
        order.setStatus(OrderStatus.getOrderStatusById(getByte(rs, "ID_ORDER_STATUS")));
        order.setOrderDate(getDate(rs, "DT_ORDER"));
        order.setCurrencyType(CurrencyType.getCurrencyTypeById(getByte(rs, "ID_CURRENCY")));
        order.setBuyValue(getFloat(rs, "VL_BUY"));
        order.setShippingValue(getFloat(rs, "VL_SHIPPING"));
        order.setTaxValue(getFloat(rs, "VL_TAX"));
        order.setCurrencyValue(getFloat(rs, "VL_CURRENCY"));
        order.setPaymentDate(getDate(rs, "DT_PAYMENT"));
        order.setPaymentStatus(PaymentStatus.getPaymentStatusById(getByte(rs, "ID_PAYMENT_STATUS")));
        order.setArriveDate(getDate(rs, "DT_ARRIVE"));
        order.setSellDate(getDate(rs, "DT_SELL"));
        order.setSellValue(getFloat(rs, "VL_SELL"));
        order.setBuyerType(BuyerType.getBuyerTypeById(getByte(rs, "ID_TYPE_BUYER")));
        order.setBuyer(getString(rs, "TX_BUYER"));
        order.setSellStatus(SellStatus.getSellStatusById(getByte(rs, "ID_SELL_STATUS")));
        order.setSellerOrderId(getString(rs, "TX_ORDER_ID"));
        order.setShippingId(getString(rs, "TX_SHIPPING_ID"));
        order.setComents(getString(rs, "TX_COMENTS"));
        //
        return order;
    }

}
